from flask_sqlalchemy import SQLAlchemy
from datetime import datetime

db = SQLAlchemy()

class Scenario(db.Model):
    """测算场景模型"""
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    description = db.Column(db.Text, nullable=True)
    type = db.Column(db.String(50), nullable=False)  # 测算类型
    db_connection_id = db.Column(db.Integer, db.ForeignKey('database_connection.id'), nullable=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # 关联
    parameters = db.relationship('CalculationParameter', backref='scenario', lazy=True)

class DatabaseConnection(db.Model):
    """数据库连接配置模型"""
    __tablename__ = 'database_connection'
    
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    db_type = db.Column(db.String(20), nullable=False)  # mysql, postgresql, sqlserver, sqlite
    host = db.Column(db.String(255), nullable=False)
    port = db.Column(db.String(10), nullable=False)
    database = db.Column(db.String(100), nullable=False)
    username = db.Column(db.String(100), nullable=False)
    password = db.Column(db.String(100), nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    # 关联
    scenarios = db.relationship('Scenario', backref='db_connection', lazy=True)
    query_history = db.relationship('QueryHistory', backref='db_connection', lazy=True)

class QueryHistory(db.Model):
    """SQL查询历史模型"""
    id = db.Column(db.Integer, primary_key=True)
    connection_id = db.Column(db.Integer, db.ForeignKey('database_connection.id'), nullable=False)
    query = db.Column(db.Text, nullable=False)
    executed_at = db.Column(db.DateTime, default=datetime.utcnow)
    success = db.Column(db.Boolean, nullable=False)
    error = db.Column(db.Text, nullable=True)

class CalculationParameter(db.Model):
    """测算参数模型"""
    id = db.Column(db.Integer, primary_key=True)
    scenario_id = db.Column(db.Integer, db.ForeignKey('scenario.id'), nullable=False)
    param_name = db.Column(db.String(100), nullable=False)
    param_value = db.Column(db.String(255), nullable=False)
    param_type = db.Column(db.String(50), nullable=False)  # basic, business, custom
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    